﻿Imports System.Data
Imports System.Data.SqlClient
Public Class itensCtasReceberDAL
#Region "Incluir/Alterar"
    Public Function inserealteraICtasRec(ByVal icrcodigo As Integer, ByVal ctrcodigo As Integer, ByVal icrdtrecebimento As Nullable(Of Date), ByVal icrvalor As Decimal,
                                   ByVal icrobs As String, ByVal pagcodigo As Integer, ByVal icrvalrecebido As Decimal, ByVal icrdesconto As Decimal,
                                   ByVal icrjam As Decimal, ByVal concodigo As Integer, ByVal stacodigo As Integer, ByVal icrparcela As Integer, ByVal icrdtvencimento As Date, ByVal icrbolcheque As String) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "execute SPItensContasReceber @icrcodigo, @ctrcodigo, @icrdtrecebimento,@icrvalor,@icrobs,@pagcodigo,@icrvalrecebido,@icrdesconto,@icrjam,@concodigo,@stacodigo,@icrparcela,@icrdtvencimento,@icrbolcheque"
            cmd.Parameters.Add(New SqlParameter("@icrcodigo", SqlDbType.Int)).Value = icrcodigo
            cmd.Parameters.Add(New SqlParameter("@ctrcodigo", SqlDbType.Int)).Value = ctrcodigo
            If (icrdtrecebimento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@icrdtrecebimento", SqlDbType.Date)).Value = icrdtrecebimento
            Else
                cmd.Parameters.Add(New SqlParameter("@icrdtrecebimento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@icrvalor", SqlDbType.Decimal)).Value = icrvalor
            cmd.Parameters.Add(New SqlParameter("@icrobs", SqlDbType.VarChar)).Value = icrobs
            cmd.Parameters.Add(New SqlParameter("@pagcodigo", SqlDbType.Int)).Value = pagcodigo
            cmd.Parameters.Add(New SqlParameter("@icrvalrecebido", SqlDbType.Decimal)).Value = icrvalrecebido
            cmd.Parameters.Add(New SqlParameter("@icrdesconto", SqlDbType.Decimal)).Value = icrdesconto
            cmd.Parameters.Add(New SqlParameter("@icrjam", SqlDbType.Decimal)).Value = icrjam
            cmd.Parameters.Add(New SqlParameter("@concodigo", SqlDbType.Int)).Value = concodigo
            cmd.Parameters.Add(New SqlParameter("@stacodigo", SqlDbType.Int)).Value = stacodigo
            cmd.Parameters.Add(New SqlParameter("@icrparcela", SqlDbType.Int)).Value = icrparcela
            cmd.Parameters.Add(New SqlParameter("@icrdtvencimento", SqlDbType.Date)).Value = icrdtvencimento
            cmd.Parameters.Add(New SqlParameter("@icrbolcheque", SqlDbType.VarChar)).Value = icrbolcheque
            ' codigo = objconexao.executacmdAI(cmd, "Ofi_ItensContasReceber")
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw New Exception("Erro de conexão", ex)
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Localizar" 'tabela itens contas a pagar
    Function localizar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0 'icr_codigo
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasReceber where icr_codigo= @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 1
                    'por codigo Financeiro.Ofi_Itens
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasReceber where icr_codigo= @codigo and sta_codigo = 4"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 2 '
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasReceber where ctr_codigo=@codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Excluir"
    Function excluir(ByVal codigo As Integer) As Boolean
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPExcluiItensCtasReceber @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            objconexao.executaCmd(cmd)
            Return True
        Catch ex As Exception
            Throw ex
            Return False
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Soma Parcelas"
    Function Somaparcelas(ByVal codigo As Integer) As Decimal 'Vendas
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(icr_valor) soma from Financeiro.Ofi_ItensContasReceber where ctr_codigo=@codigo and sta_codigo=4"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function
#End Region
End Class
